package com.qf.dao.impl;

import com.qf.dao.Showselect;
import com.qf.domain.Goods;
import com.qf.domain.GoodsType;
import com.qf.domain.Order;
import com.qf.domain.User;
import com.qf.utils.DataSourceUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class ShowselectImpl implements Showselect {
    private QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());

    @Override
    public List<User> selectUserList(String username, String gender) {
        try {
            if ((username == null || username == "") && (gender == null || gender == "")) {
                String sql = " select * from db_shopping.tb_user where role=1";
                return queryRunner.query(sql, new BeanListHandler<>(User.class));
            } else if (username == null || username == "") {
                String sql = " select * from db_shopping.tb_user where gender=? and role=1  ";
                return queryRunner.query(sql, new BeanListHandler<>(User.class), gender);
            } else if (gender == null || gender == "") {
                String sql = " select * from db_shopping.tb_user where username=? and role=1  ";
                return queryRunner.query(sql, new BeanListHandler<>(User.class), username);
            } else {
                String sql = " select * from db_shopping.tb_user where username=? and gender=?and role=1  ";
                return queryRunner.query(sql, new BeanListHandler<>(User.class), username, gender);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<User> selectinvalidUser(String username, String gender) {
        try {
            if ((username == null || username == "") && (gender == null || gender == "")) {
                String sql = " select * from db_shopping.tb_user where role=2";
                return queryRunner.query(sql, new BeanListHandler<>(User.class));
            } else if (username == null || username == "") {
                String sql = " select * from db_shopping.tb_user where gender=? and role=2  ";
                return queryRunner.query(sql, new BeanListHandler<>(User.class), gender);
            } else if (gender == null || gender == "") {
                String sql = " select * from db_shopping.tb_user where username=? and role=2  ";
                return queryRunner.query(sql, new BeanListHandler<>(User.class), username);
            } else {
                String sql = " select * from db_shopping.tb_user where username=? and gender=?and role=2  ";
                return queryRunner.query(sql, new BeanListHandler<>(User.class), username, gender);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<GoodsType> selectGoodsType(String name, String level) {
        try {
            if ((name == null || name == "") && (level == null || level == "")) {
                String sql = " select * from db_shopping.tb_goods_type ";
                return queryRunner.query(sql, new BeanListHandler<>(GoodsType.class));
            } else if (name == null || name == "") {
                String sql = " select * from db_shopping.tb_goods_type where level=? ";
                return queryRunner.query(sql, new BeanListHandler<>(GoodsType.class), level);
            } else if (level == null || level == "") {
                String sql = " select * from db_shopping.tb_goods_type where name=? ";
                return queryRunner.query(sql, new BeanListHandler<>(GoodsType.class), name);
            } else {
                String sql = " select * from db_shopping.tb_goods_type where name=? and level=? ";
                return queryRunner.query(sql, new BeanListHandler<>(GoodsType.class), name, level);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Goods> selectGoods(String name) {
        try {
            String sql = " select * from db_shopping.tb_goods where name=? ";
            return queryRunner.query(sql, new BeanListHandler<>(Goods.class), name);
        }catch(Exception e)
    {
        throw new RuntimeException(e);
    }
}

    @Override
    public List<Order> selectOrder(Integer uid, String status) {
        try {
            switch (status) {
                case "0" -> {
                    String sql = " select * from db_shopping.tb_order where uid=?";
                    return queryRunner.query(sql, new BeanListHandler<>(Order.class),uid);
                }
                case "1" -> {
                    String sql = " select * from db_shopping.tb_order where uid=? and status=?";
                    return queryRunner.query(sql, new BeanListHandler<>(Order.class), uid, status);
                }
                case "2" -> {
                    String sql = " select * from db_shopping.tb_order where uid=? and status=?";
                    return queryRunner.query(sql, new BeanListHandler<>(Order.class), uid, status);
                }
                case "3" -> {
                    String sql = " select * from db_shopping.tb_order where uid=? and status=?";
                    return queryRunner.query(sql, new BeanListHandler<>(Order.class), uid, status);
                }
                case "4" -> {
                    String sql = " select * from db_shopping.tb_order where uid=? and status=?";
                    return queryRunner.query(sql, new BeanListHandler<>(Order.class), uid, status);
                }
                case "5" -> {
                    String sql = " select * from db_shopping.tb_order where uid=? and status=?";
                    return queryRunner.query(sql, new BeanListHandler<>(Order.class), uid, status);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return null;
    }

    @Override
    public User selectUser(String username) {
        String sql = " select * from db_shopping.tb_user where username=? ";
        try {
            return queryRunner.query(sql, new BeanHandler<>(User.class), username);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Goods selectGoodsById(Integer id) {
        String sql = " SELECT  * FROM db_shopping.tb_goods where id=? ";
        try {
            return queryRunner.query(sql,new BeanHandler<>(Goods.class), id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public GoodsType selectById(Integer typeid) {
        String sql=" SELECT `id`,`name`,`level`,`parent` FROM db_shopping.`tb_goods_type` WHERE `id`=? ";
        try {
            return queryRunner.query(sql,new BeanHandler<>(GoodsType.class),typeid);
        } catch (SQLException e) {
            throw new RuntimeException(e);        }

    }

    @Override
    public GoodsType selectgoodById(Integer typeid) {
        String sql=" SELECT `id`,`name`,`level`,`parent` FROM db_shopping.`tb_goods_type` WHERE `id`=? ";
        try {
            return queryRunner.query(sql,new BeanHandler<>(GoodsType.class),typeid);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<GoodsType> selectGoodsTypefinall() {
        try {
            return queryRunner.query("select * from db_shopping.tb_goods_type",new BeanListHandler<>(GoodsType.class));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Order> selectOrderByname(Integer uid, String status) {
        try {
           if (status.equals("0")) {
                String sql = " select * from db_shopping.tb_order where uid=? ";
                return queryRunner.query(sql, new BeanListHandler<>(Order.class),uid);
            } else {
                String sql = " select * from db_shopping.tb_order where uid=? and status=?  ";
                return queryRunner.query(sql, new BeanListHandler<>(Order.class),uid,status);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}
